package com.yc.damai.dao;

import java.sql.*;
import java.util.List;

import com.yc.damai.bean.Orderitem;
import com.yc.damai.bean.Orders;
import com.yc.damai.util.DBHelper;

public class OrdersDao {
	
	public void addOrders(Orders orders, List<Orderitem> orderitems) throws SQLException {
		
		// 原因:  1. 要获取到自增值;  2. 确保在一个事务中执行(使用一个连接)
		Connection conn = DBHelper.openConnection();
		
		// 关闭自动提交
		conn.setAutoCommit(false);
		
		try {
			String sql1 = "insert into orders values (default,null,now(),1,?,?,?,?)";
			String sql2 = "insert into orderitem values (default,?,0,?,?)";
			String sql3 = "UPDATE orderitem a\n" +
					"JOIN product b ON a.pid = b.pid\n" +
					"SET a.subtotal = a.count * b.shop_price\n" +
					"WHERE\n" +
					"	a.oid = ?";
			String sql4 = "UPDATE orders a\n" +
					"SET a.total = (\n" +
					"	SELECT\n" +
					"		sum(subtotal)\n" +
					"	FROM\n" +
					"		orderitem a\n" +
					"	WHERE\n" +
					"		a.oid = ?\n" +
					")\n" +
					"WHERE\n" +
					"	oid = ?";
			
			
			// 新增订单, 创建获取自增值的语句对象
			PreparedStatement ps = conn.prepareStatement(sql1,Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, orders.getAddr());
			ps.setString(2, orders.getPhone());
			ps.setInt(3, orders.getUid());
			ps.setString(4, orders.getName());
			ps.executeUpdate();
			// 获取自增值结果集
			ResultSet rs = ps.getGeneratedKeys();
			rs.next();
			// 获取自增列的值
			int oid = rs.getInt(1);
			
			// 构建新的语句对象
			ps = conn.prepareStatement(sql2);
			// 循环新增订单明细
			// 获取订单主键值  , 总金额现在无法计算
			for(Orderitem oi : orderitems) {
				ps.setInt(1, oi.getCount());
				ps.setInt(2, oi.getPid());
				ps.setInt(3, oid);
				ps.executeUpdate();
			}
			
			ps = conn.prepareStatement(sql3);
			ps.setInt(1, oid);
			ps.executeUpdate();
			
			ps = conn.prepareStatement(sql4);
			ps.setInt(1, oid);
			ps.setInt(2, oid);
			ps.executeUpdate();
			
			/**
			 * 购物车删除已经购买的商品
			 */
			
			// 提交事务
			conn.commit();
		} catch (SQLException e) {
			// 回滚事务
			conn.rollback();
			throw e;
		} finally {
			conn.close();
		}
		
	}

}
